const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

async function initConfigTable() {
  let connection;
  
  try {
    // 使用环境变量连接数据库
    connection = await mysql.createConnection({
      host: process.env.DB_HOST,
      port: process.env.DB_PORT,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME
    });

    console.log('数据库连接成功');

    // 读取SQL文件
    const sqlPath = path.join(__dirname, '../database/create_config_table.sql');
    const sqlContent = fs.readFileSync(sqlPath, 'utf8');

    // 分割SQL语句并执行
    const statements = sqlContent
      .split(';')
      .map(stmt => stmt.trim())
      .filter(stmt => stmt.length > 0);

    for (const statement of statements) {
      if (statement.trim()) {
        try {
          await connection.execute(statement);
          console.log('SQL语句执行成功');
        } catch (error) {
          if (error.code === 'ER_DUP_ENTRY') {
            console.log('配置已存在，跳过插入');
          } else {
            console.error('SQL执行错误:', error.message);
          }
        }
      }
    }

    // 验证表是否创建成功
    const [tables] = await connection.execute(
      "SHOW TABLES LIKE 'system_config'"
    );

    if (tables.length > 0) {
      console.log('✅ system_config表创建成功');
      
      // 检查配置数量
      const [count] = await connection.execute(
        'SELECT COUNT(*) as count FROM system_config'
      );
      console.log(`📊 配置表中有 ${count[0].count} 条配置记录`);
    } else {
      console.error('❌ system_config表创建失败');
    }

  } catch (error) {
    console.error('初始化配置表失败:', error);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('数据库连接已关闭');
    }
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  require('dotenv').config({ path: './backend/.env' });
  initConfigTable()
    .then(() => {
      console.log('🎉 配置表初始化完成');
      process.exit(0);
    })
    .catch((error) => {
      console.error('初始化失败:', error);
      process.exit(1);
    });
}

module.exports = { initConfigTable };
